import pandas as pd

# 测试打开文件
# file_path = r"D:\desktop.u.mine\d1120801818-00_bill_20240108.xlsx"
# df = pd.read_excel(file_path)
#
# print(df)

import pandas as pd

# 读取excel文件
file_path = "D:\desktop.u.mine\d1120801818-00_bill_20240108 - 副本.xlsx"
excel_file = pd.ExcelFile(file_path)





import pandas as pd

# 读取excel文件
file_path = "D:\desktop.u.mine\d1120801818-00_bill_20240108 - 副本.xlsx"
excel_file = pd.ExcelFile(file_path)

# 读取SHEET1、SHEET2和SHEET3
sheet1 = excel_file.parse("SHEET1")
sheet2 = excel_file.parse("SHEET2")
sheet3 = pd.DataFrame()

# 遍历SHEET2中第一列数据
for data in sheet2.iloc[:, 0]:
    # 在SHEET1的第一列中查找数据
    found_row = sheet1[sheet1.iloc[:, 0] == data]
    if not found_row.empty:
        # 将找到的数据整行复制到SHEET3
        sheet3 = sheet3._append(found_row)

# 将SHEET3写入新的excel文件
output_file_path = "D:\desktop.u.mine\d1120801818-00_bill_20240108 - 副本.xlsx"
with pd.ExcelWriter(output_file_path) as writer:
    sheet3.to_excel(writer, sheet_name="SHEET3", index=False)

df1 = pd.read_excel('1.xlsx', sheet_name='SHEET1')
df2 = pd.read_excel('1.xlsx', sheet_name='SHEET2')

df1['號碼'] = df1['號碼'].astype(str).apply(lambda x: x.strip())

df2['LIS'] = df2['號碼'].astype(str).apply(lambda x: x.strip())

common_values = df1[df1['號碼'].isin(df2['LIS'])]

# 展示满足条件的行
# print(common_values)

# 生成到第三个sheet页
with pd.ExcelWriter('2.xlsx', engine='openpyxl', mode='a') as writer:
    common_values.to_excel(writer, sheet_name='SHEET3', index=False)

